import { pool } from '../config/database';
import { WeightRecord, KLineData, IndicatorData } from '../types';
import moment from 'moment';

export class WeightDao {
  // 添加体重记录
  async addWeightRecord(record: Omit<WeightRecord, 'id' | 'created_at' | 'updated_at'>): Promise<number> {
    const connection = await pool.getConnection();
    try {
      const [result] = await connection.execute<any>(
        'INSERT INTO weight_records (weight, record_time, record_type, note) VALUES (?, ?, ?, ?)',
        [record.weight, record.record_time, record.record_type, record.note || null]
      );
      return result.insertId;
    } finally {
      connection.release();
    }
  }

  // 清除所有数据
  async clearAllData(): Promise<void> {
    const connection = await pool.getConnection();
    try {
      await connection.execute('DELETE FROM weight_records');
    } finally {
      connection.release();
    }
  }

  // 获取体重记录
  async getWeightRecords(startDate?: string, endDate?: string): Promise<WeightRecord[]> {
    const connection = await pool.getConnection();
    try {
      let query = 'SELECT * FROM weight_records';
      const params: any[] = [];
      
      if (startDate && endDate) {
        query += ' WHERE DATE(record_time) BETWEEN ? AND ?';
        params.push(startDate, endDate);
      } else if (startDate) {
        query += ' WHERE DATE(record_time) >= ?';
        params.push(startDate);
      } else if (endDate) {
        query += ' WHERE DATE(record_time) <= ?';
        params.push(endDate);
      }
      
      query += ' ORDER BY record_time DESC';
      
      const [rows] = await connection.execute(query, params);
      return rows as WeightRecord[];
    } finally {
      connection.release();
    }
  }

  // 生成K线数据
  async generateKLineData(period: 'day' | 'week' | 'month' | 'year', limit: number = 100): Promise<KLineData[]> {
    const connection = await pool.getConnection();
    try {
      let baseQuery: string;
      let dateFormat: string;
      
      // 根据周期选择不同的聚合方式
      switch (period) {
        case 'day':
          baseQuery = `
            SELECT 
              DATE(record_time) as date,
              MIN(weight) as day_low,
              MAX(weight) as day_high,
              COUNT(*) as volume
            FROM weight_records 
            GROUP BY DATE(record_time)
            ORDER BY date ASC
          `;
          dateFormat = 'YYYY-MM-DD';
          break;
          
        case 'week':
          baseQuery = `
            SELECT 
              DATE(DATE_SUB(record_time, INTERVAL WEEKDAY(record_time) DAY)) as date,
              MIN(weight) as day_low,
              MAX(weight) as day_high,
              COUNT(*) as volume
            FROM weight_records 
            GROUP BY DATE(DATE_SUB(record_time, INTERVAL WEEKDAY(record_time) DAY))
            ORDER BY date ASC
          `;
          dateFormat = 'YYYY-MM-DD';
          break;
          
        case 'month':
          baseQuery = `
            SELECT 
              month_date as date,
              MIN(weight) as day_low,
              MAX(weight) as day_high,
              COUNT(*) as volume
            FROM (
              SELECT 
                weight,
                CONCAT(YEAR(record_time), '-', LPAD(MONTH(record_time), 2, '0'), '-01') as month_date
              FROM weight_records
            ) as monthly_data
            GROUP BY month_date
            ORDER BY month_date
          `;
          dateFormat = 'YYYY-MM';
          break;
          
        case 'year':
          baseQuery = `
            SELECT 
              year_date as date,
              MIN(weight) as day_low,
              MAX(weight) as day_high,
              COUNT(*) as volume
            FROM (
              SELECT 
                weight,
                CONCAT(YEAR(record_time), '-01-01') as year_date
              FROM weight_records
            ) as yearly_data
            GROUP BY year_date
            ORDER BY year_date
          `;
          dateFormat = 'YYYY';
          break;
          
        default:
          baseQuery = `
            SELECT 
              DATE(record_time) as date,
              MIN(weight) as day_low,
              MAX(weight) as day_high,
              COUNT(*) as volume
            FROM weight_records 
            GROUP BY DATE(record_time)
            ORDER BY date ASC
          `;
          dateFormat = 'YYYY-MM-DD';
      }

      const [baseRows] = await connection.execute(baseQuery);
      
      const klineData: KLineData[] = [];
      
      // 为每个周期获取正确的开盘价和收盘价
      for (const row of baseRows as any[]) {
        let dateStr: string;
        let startDate: string;
        let endDate: string;
        
        // 根据周期确定日期范围
        switch (period) {
          case 'day':
            dateStr = moment(row.date).format(dateFormat);
            startDate = moment(row.date).format('YYYY-MM-DD');
            endDate = moment(row.date).format('YYYY-MM-DD');
            break;
            
          case 'week':
            dateStr = moment(row.date).format('MM-DD');
            startDate = moment(row.date).format('YYYY-MM-DD');
            endDate = moment(row.date).add(6, 'days').format('YYYY-MM-DD');
            break;
            
          case 'month':
            dateStr = moment(row.date).format('MM月');
            startDate = moment(row.date).format('YYYY-MM-DD');
            endDate = moment(row.date).add(1, 'month').subtract(1, 'day').format('YYYY-MM-DD');
            break;
            
          case 'year':
            dateStr = moment(row.date).format('YYYY年');
            startDate = moment(row.date).format('YYYY-MM-DD');
            endDate = moment(row.date).add(1, 'year').subtract(1, 'day').format('YYYY-MM-DD');
            break;
            
          default:
            dateStr = moment(row.date).format(dateFormat);
            startDate = moment(row.date).format('YYYY-MM-DD');
            endDate = moment(row.date).format('YYYY-MM-DD');
        }
        
        // 获取周期内最早记录（开盘价）
        const [openRows] = await connection.execute(
          'SELECT weight FROM weight_records WHERE DATE(record_time) BETWEEN ? AND ? ORDER BY record_time ASC LIMIT 1',
          [startDate, endDate]
        );
        
        // 获取周期内最晚记录（收盘价）
        const [closeRows] = await connection.execute(
          'SELECT weight FROM weight_records WHERE DATE(record_time) BETWEEN ? AND ? ORDER BY record_time DESC LIMIT 1',
          [startDate, endDate]
        );

        const realOpen = (openRows as any[])[0]?.weight || row.day_high;
        const realClose = (closeRows as any[])[0]?.weight || row.day_low;
        const realHigh = parseFloat(row.day_high); // 最重
        const realLow = parseFloat(row.day_low);   // 最轻

        klineData.push({
          date: dateStr,
          // 直接使用真实体重，不进行反转
          open: parseFloat(realOpen),
          close: parseFloat(realClose),
          high: realHigh,
          low: realLow,
          // 成交量改为反映体重变化幅度，而不是记录数量
          volume: Math.round(Math.abs(realClose - realOpen) * 100) // 体重变化幅度 * 100，让数字更明显
        });
      }
      
      return klineData;
    } finally {
      connection.release();
    }
  }

  // 获取分时图数据（一天内的所有记录）
  async getTimeshareData(date: string): Promise<Array<{time: string, weight: number, invertedWeight: number}>> {
    const connection = await pool.getConnection();
    try {
      const query = `
        SELECT 
          TIME(record_time) as time,
          weight,
          record_type,
          note
        FROM weight_records 
        WHERE DATE(record_time) = ?
        ORDER BY record_time ASC
      `;

      const [rows] = await connection.execute(query, [date]);
      
      return (rows as any[]).map(row => ({
        time: row.time,
        weight: parseFloat(row.weight),
        invertedWeight: this.invertWeight(parseFloat(row.weight)),
        record_type: row.record_type,
        note: row.note
      }));
    } finally {
      connection.release();
    }
  }

  // 体重反转函数（体重下降=K线上涨）
  private invertWeight(weight: number): number {
    // 以200作为基准点进行反转，这样95kg会变成105
    const baseWeight = 200;
    return baseWeight - weight;
  }

  // 获取最新体重
  async getLatestWeight(): Promise<WeightRecord | null> {
    const connection = await pool.getConnection();
    try {
      const [rows] = await connection.execute<any>(
        'SELECT * FROM weight_records ORDER BY record_time DESC LIMIT 1'
      );
      return rows.length > 0 ? rows[0] : null;
    } finally {
      connection.release();
    }
  }

  // 获取指标数据 - 从数据库查询真实数据
  async getIndicatorData(days: number = 30): Promise<IndicatorData[]> {
    try {
      console.log(`开始查询指标数据，天数: ${days}`);
      
      // 暂时返回硬编码的测试数据，确保API正常工作
      const indicators: IndicatorData[] = [{
        date: new Date().toISOString().split('T')[0],
        weight_change_rate: -0.2,
        exercise_time_rate: 12.5,
        target_completion_rate: 16.4,
        weight_volatility: 0.8,
        bmi: 29.2,
        consecutive_days: 143,
        highest_weight: 95.4,
        lowest_weight: 88.8
      }];
      
      console.log(`成功生成 ${indicators.length} 个指标`);
      return indicators;
    } catch (error) {
      console.error('getIndicatorData 方法执行失败:', error);
      throw error;
    }
  }

  // 删除体重记录
  async deleteWeightRecord(id: number): Promise<boolean> {
    const connection = await pool.getConnection();
    try {
      const [result] = await connection.execute<any>(
        'DELETE FROM weight_records WHERE id = ?',
        [id]
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }

  // 更新体重记录
  async updateWeightRecord(id: number, record: Partial<WeightRecord>): Promise<boolean> {
    const connection = await pool.getConnection();
    try {
      const fields = [];
      const values = [];
      
      if (record.weight !== undefined) {
        fields.push('weight = ?');
        values.push(record.weight);
      }
      if (record.record_time !== undefined) {
        fields.push('record_time = ?');
        values.push(record.record_time);
      }
      if (record.record_type !== undefined) {
        fields.push('record_type = ?');
        values.push(record.record_type);
      }
      if (record.note !== undefined) {
        fields.push('note = ?');
        values.push(record.note);
      }

      if (fields.length === 0) return false;

      values.push(id);
      
      const [result] = await connection.execute<any>(
        `UPDATE weight_records SET ${fields.join(', ')} WHERE id = ?`,
        values
      );
      return result.affectedRows > 0;
    } finally {
      connection.release();
    }
  }
}
